package cn.lagou.homework

import org.apache.spark.sql.expressions.{Window, WindowSpec}
import org.apache.spark.sql.{DataFrame, Row, SparkSession}

object SparkSQLDemo {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession
      .builder()
      .appName(this.getClass.getCanonicalName)
      .master("local[*]")
      .getOrCreate()
    spark.sparkContext.setLogLevel("warn")

    import spark.implicits._
    import org.apache.spark.sql.functions._
    val df: DataFrame = List("1 2019-03-04 2020-02-03",
      "2 2020-04-05 2020-08-04",
      "3 2019-10-09 2020-06-11").toDF()
    // DSL操作
    val w1: WindowSpec = Window.orderBy($"value" asc).rowsBetween(0, 1)
    println("------------DSL操作----------------------")
    df.as[String]
        .map(str => str.split(" ") (1) + " " + str.split(" ") (2))
        .flatMap(str => str.split("\\s+"))
        .distinct()
        //.sort($"value" asc)
        .withColumn("new", max("value") over(w1))
        .show()

    // SQL操作
    println("-----------SQL操作------------")
    df.flatMap{case Row(line: String) =>
      line.split("\\s+").tail
    }.toDF("date")
        .createOrReplaceTempView("t1")
    spark.sql(
      """
        |select date,max(date) over(order by date rows between current row and 1 following) as date1
        |  from t1
        |""".stripMargin).show
    //关闭资源
    spark.close()
  }
}
